package com.jwtter.generator;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.alibaba.fastjson.JSON;
import com.jwtter.generator.db.DBConnectionUtil;
import com.jwtter.generator.db.sql.AllTabColumns;
import com.jwtter.generator.db.sql.AllTable;
import com.jwtter.generator.db.sql.UserColComments;
import com.jwtter.generator.db.sql.UserTabComment;
import com.jwtter.generator.db.sql.UserTable;
import com.jwtter.generator.utils.LogUtil;

/**
 * @date 2017年6月15日
 * @author jinwt
 *
 */
public class TableUtils {

	// private LogUtil logUtil = new LogUtil(this.getClass());

	public static ResultSet getResultSet(Connection connection, String sql) {
		Statement statement;
		try {
			statement = connection.createStatement();
			return statement.executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	/**
	 * getAllTables
	 * 
	 * @param connection
	 * @return List<AllTable>
	 */
	public static List<AllTable> getAllTables(Connection connection) {
		List<AllTable> list = new ArrayList<>();
		try {
			String sql = "select * from all_tables";
			ResultSet resultSet = getResultSet(connection, sql);
			while (resultSet.next()) {
				AllTable table = new AllTable();
				table.setOwner(resultSet.getString("OWNER"));
				list.add(table);
			}
			LogUtil.info(JSON.toJSONString(list));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 数据库表属性信息
	 * 
	 * @param connection
	 * @return
	 */
	public static List<UserTable> getUserTables(Connection connection) {
		List<UserTable> list = new ArrayList<>();
		try {
			String sql = "select * from all_tables";
			ResultSet resultSet = getResultSet(connection, sql);
			while (resultSet.next()) {
				UserTable userTable = new UserTable();
				userTable.setTable_name(resultSet.getString("TABLE_NAME"));
				list.add(userTable);
			}
			LogUtil.info(JSON.toJSONString(list));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 获取Table 的注释 Comment
	 * 
	 * @param connection
	 * @return
	 */
	public static List<UserTabComment> getUserTabComments(Connection connection) {
		List<UserTabComment> list = new ArrayList<>();
		try {
			String sql = "select * from user_tab_comments";
			ResultSet resultSet = getResultSet(connection, sql);
			while (resultSet.next()) {
				UserTabComment userTabComment = new UserTabComment();
				userTabComment.setTableName(resultSet.getString("TABLE_NAME"));
				userTabComment.setTableType(resultSet.getString("TABLE_TYPE"));
				userTabComment.setComments(resultSet.getString("COMMENTS"));
				list.add(userTabComment);
			}
			LogUtil.info(JSON.toJSONString(list));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 获取Table中的Column 的 Comment
	 * 
	 * @param connection
	 * @param tableName
	 * @return
	 */
	public static List<UserColComments> getUserColComments(Connection connection, String tableName) {
		List<UserColComments> list = new ArrayList<>();
		try {
			String sql = "select * from user_col_comments where TABLE_NAME ='" + tableName + "'";
			ResultSet resultSet = getResultSet(connection, sql);
			while (resultSet.next()) {
				UserColComments userColComments = new UserColComments();
				userColComments.setTableName(resultSet.getString("TABLE_NAME"));
				userColComments.setColumnName(resultSet.getString("COLUMN_NAME"));
				userColComments.setComments(resultSet.getString("COMMENTS"));
				list.add(userColComments);
			}
			LogUtil.info(JSON.toJSONString(list));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * 获取Table中的Column属性
	 * 
	 * @param connection
	 * @param tableName
	 * @return
	 */
	public static List<AllTabColumns> getAllTabColumns(Connection connection, String tableName) {
		List<AllTabColumns> list = new ArrayList<>();
		try {
			String sql = "select * from all_tab_columns where TABLE_NAME ='" + tableName + "'";
			ResultSet resultSet = getResultSet(connection, sql);
			while (resultSet.next()) {
				AllTabColumns allTabColumns = new AllTabColumns();
				allTabColumns.setTABLE_NAME(resultSet.getString("TABLE_NAME"));// 表名
				allTabColumns.setCOLUMN_NAME(resultSet.getString("COLUMN_NAME"));// 列名
				allTabColumns.setDATA_TYPE(resultSet.getString("DATA_TYPE"));// 类型
				allTabColumns.setDATA_SCALE(resultSet.getInt("DATA_SCALE"));// number 精度
				// userColComments.setComments(resultSet.getString("COMMENTS"));
				// userColComments.setAVG_COL_LEN(aVG_COL_LEN);
				// userColComments.setCHAR_COL_DECL_LENGTH(cHAR_COL_DECL_LENGTH);
				// userColComments.setCHAR_LENGTH(cHAR_LENGTH);
				// userColComments.setCHARACTER_SET_NAME(cHARACTER_SET_NAME);
				// userColComments.setCOLUMN_ID(cOLUMN_ID);
				// userColComments.setCOLUMN_NAME(cOLUMN_NAME);
				// userColComments.setDATA_DEFAULT(dATA_DEFAULT);
				// userColComments.setDATA_LENGTH(dATA_LENGTH);
				// userColComments.setDATA_PRECISION(dATA_PRECISION);
				// userColComments.setDATA_TYPE_MOD(dATA_TYPE_MOD);
				// userColComments.setDATA_TYPE_OWNER(dATA_TYPE_OWNER);
				// userColComments.setDATA_UPGRADED(dATA_UPGRADED);
				// userColComments.setDEFAULT_LENGTH(dEFAULT_LENGTH);
				// userColComments.setDENSITY(dENSITY);
				// userColComments.setGLOBAL_STATS(gLOBAL_STATS);
				// userColComments.setHIGH_VALUE(hIGH_VALUE);
				// userColComments.setHISTOGRAM(hISTOGRAM);
				// userColComments.setLAST_ANALYZED(lAST_ANALYZED);
				// userColComments.setLOW_VALUE(lOW_VALUE);
				// userColComments.setNULLABLE(nULLABLE);
				// userColComments.setNUM_BUCKETS(nUM_BUCKETS);
				// userColComments.setNUM_DISTINCT(nUM_DISTINCT);
				list.add(allTabColumns);
			}
			LogUtil.info(JSON.toJSONString(list));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public static void main(String[] args) {
		String driverClassName = "oracle.jdbc.OracleDriver";
		String url = "jdbc:oracle:thin:@10.10.68.91:1521:orcl";
		String username = "pdfbtest";
		String password = "pdfbtest";
		Connection connection = DBConnectionUtil.getConnection(driverClassName, url, username, password);
		getUserTabComments(connection);
	}
}
